﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL;
using ECS.DAL.Component;
using ECS.BLL.Entity;
using ECS.Framework;

namespace ECS.BLL.Component
{
    public class SearchInfo
    {
        #region private properties

        #endregion

        #region private methods

        #endregion
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fblFlightNo"></param>
        /// <param name="currentFBLFlightNo"></param>
        /// <returns></returns>
        #region public methods
        public string CheckFBL(string carrier,string fblFlightNo, string currentFBLFlightNo)
        {
           string data = string.Empty;
           StringBuilder sqlString = new StringBuilder();
           using(Entities en = new Entities())
           {
               en.Open();
               sqlString.Append("select dbo.usf_check_fbl_parameters(@carrier,@FlightNo,@currentFBLFlightNo) as [checkFBLStatus]");
               sqlString.Replace("@carrier", BaseSQLModel.SqlWithQoute(carrier));
               sqlString.Replace("@FlightNo", BaseSQLModel.SqlWithQoute(fblFlightNo));
               sqlString.Replace("@currentFBLFlightNo", BaseSQLModel.SqlWithQoute(currentFBLFlightNo));
               DbCommand cmd = en.CreateCommand(sqlString.ToString());
               DbDataReader reader = cmd.ExecuteReader();
               if (reader.Read())
               {
                   data = reader["checkFBLStatus"].ToString();
               }
           }
           return data;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dtInput"></param>
        /// <returns></returns>
        public string GetDateFormat(DateTime dtInput)
        {
            string data = string.Empty;
            StringBuilder sqlString = new StringBuilder();
            using (Entities entity = new Entities())
            {
                sqlString.Append("select dbo.usf_format_flight_date(@date) as [Flight Date Display]");
                sqlString.Replace("@date", BaseSQLModel.SqlWithQoute(dtInput.ToString("yyyy-MM-dd HH:mm:ss")));
                entity.Open();
                DbCommand cmd = entity.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    data = reader["Flight Date Display"].ToString();
                }
            }
            return data;
        }
        /// <summary>
        /// Get Application current version on Sever
        /// </summary>
        /// <returns></returns>
        public string GetApplicationVersion()
        {
            string versionNo = string.Empty;
            using (Entities entity = new Entities())
            {
                entity.Open();
                DbCommand cmd = entity.CreateCommand("SELECT app_version FROM t_com_application WHERE app_id='EICS PC'");
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    versionNo = reader["app_version"].ToString();
                }
            }
            return versionNo;
        }
        /// <summary>
        /// get scale Id by station id
        /// </summary>
        /// <param name="stationId"></param>
        /// <returns></returns>
        public string GetScaleId(string device)
        {
            string scaleId = string.Empty;
            StringBuilder sqlString = new StringBuilder();
            using (Entities entity = new Entities())
            {
                sqlString.Append("SELECT setting_value_string FROM t_eics_setup WHERE setting_name = 'SCALE ID' AND station_id = @stationID");
                sqlString.Replace("@stationID", BaseSQLModel.SqlWithQoute(device));
                entity.Open();
                DbCommand cmd = entity.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    scaleId = reader["setting_value_string"].ToString();
                }
            }
            return scaleId;
        }
        /// <summary>
        /// get FBL flight Number by station id
        /// </summary>
        /// <param name="device"></param>
        /// <returns></returns>
        public string getFBLFlihtNo(string device)
        {
            string data = string.Empty;
            StringBuilder sqlString = new StringBuilder();
            using (Entities entity = new Entities())
            {
                sqlString.Append("SELECT setting_value_string FROM t_eics_setup WHERE setting_name = 'FBL_FLT_NO' AND station_id = @stationID");
                sqlString.Replace("@stationID", BaseSQLModel.SqlWithQoute(device));
                entity.Open();
                DbCommand cmd = entity.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    data = reader["setting_value_string"].ToString();
                }
            }
            return data;
        }
        public string GetBFLPath()
        {
            string data = string.Empty;
            StringBuilder sqlString = new StringBuilder();
            using (Entities entity = new Entities())
            {
                sqlString.Append("SELECT setting_value_string FROM t_eics_setup WHERE setting_name = 'FBL_FOLDER'");
                entity.Open();
                DbCommand cmd = entity.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    data = reader["setting_value_string"].ToString();
                }
            }
            return data;
        }

        /// <summary>
        /// search weight slip all parameter
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchWeightSlip(SearchCriteria parameter)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append("usp_SearchWeightSlip");

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));

                if (parameter.FlightDate == DateTime.MinValue)
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", null));
                }
                else
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", (string.Format("{0}", parameter.MAWB))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", (string.Format("{0}", parameter.MAWBPrefix))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", (string.Format("{0}", parameter.MAWBSerial))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", (string.Format("{0}", parameter.HAWB))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", (string.Format("{0}", parameter.Carrier))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", (string.Format("{0}", parameter.FlightNo))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", (string.Format("{0}", parameter.AgentCode))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", (string.Format("{0}", parameter.ScaleId))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", (string.Format("{0}", parameter.SlipNo))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentype", (parameter.SipmentType)));

                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.MAWB))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.MAWBPrefix))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.MAWBSerial))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.HAWB))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.Carrier))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.FlightNo))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.AgentCode))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.ScaleId))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", BaseSQLModel.SqlWithQoute(string.Format("{0}", parameter.SlipNo))));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentype", BaseSQLModel.SqlWithQoute(parameter.SipmentType)));

                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);

                //cmd.Parameters.AddWithValue("@in_vchMAWB", txtPrefix.Text + "-" + txtSerial.Text);
                //cmd.Parameters.AddWithValue("@in_vchMAWBPrefix", "%" + txtPrefix.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchMAWBSerial", "%" + txtSerial.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchHAWB", "%" + txtHAWB.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchCarrier", "%" + txtCarrier.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchFlightNumber", "%" + txtFltNo.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchAgentCode", "%" + txtAgentCode.Text);
                //cmd.Parameters.AddWithValue("@in_vchScaleID", "%" + txtScaleID.Text);
                //cmd.Parameters.AddWithValue("@in_vchSlipNumber", "%" + txtSlipNumber.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchShipmentype", comboboxst);
            }
            return ds;
        }
        /// <summary>
        /// search weight slip include flight date
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchWeightSlipWithDateRange(SearchCriteria parameter, string fromDateTime, string toDateTime)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append("usp_SearchWeightSlipWithDate");

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                if (parameter.FlightDate == DateTime.MinValue)
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", null));
                }
                else
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", parameter.MAWB));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MAWBPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MAWBSerial));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.HAWB));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", parameter.Carrier));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", parameter.FlightNo));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", parameter.AgentCode));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", parameter.ScaleId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", parameter.SlipNo));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentype", parameter.SipmentType));

                cmd.Parameters.Add(entities.CreateParameter("@in_dtFromDateTime", fromDateTime));
                cmd.Parameters.Add(entities.CreateParameter("@in_dtToDateTime", toDateTime));
                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);

                //cmd.Parameters.AddWithValue("@in_vchApplicationID", appId);
                //cmd.Parameters.AddWithValue("@in_dtFlightDate", dpkFlightDate.Value);
                //cmd.Parameters.AddWithValue("@in_vchDevice", device);
                //cmd.Parameters.AddWithValue("@in_vchUserID", GlobalVariable.userName);
                //cmd.Parameters.AddWithValue("@in_vchMAWB", txtPrefix.Text + "-" + txtSerial.Text);
                //cmd.Parameters.AddWithValue("@in_vchMAWBPrefix", txtPrefix.Text);
                //cmd.Parameters.AddWithValue("@in_vchMAWBSerial", txtSerial.Text);
                //cmd.Parameters.AddWithValue("@in_vchHAWB", txtHAWB.Text);
                //cmd.Parameters.AddWithValue("@in_vchCarrier", txtCarrier.Text);
                //cmd.Parameters.AddWithValue("@in_vchFlightNumber", txtFltNo.Text);
                //cmd.Parameters.AddWithValue("@in_vchAgentCode", txtAgentCode.Text);
                //cmd.Parameters.AddWithValue("@in_vchScaleID", txtScaleID.Text);
                //cmd.Parameters.AddWithValue("@in_vchSlipNumber", txtSlipNumber.Text);
                //cmd.Parameters.AddWithValue("@in_vchShipmentype", comboboxst);
            }
            return ds;
        }
        /// <summary>
        /// search warehouse inventory
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchWhInventory(SearchCriteria parameter)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append("usp_SearchWHInventory");

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                if (parameter.FlightDate == DateTime.MinValue)
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", null));
                }
                else
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", (string.Format("{0}", parameter.MAWB))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", (string.Format("{0}", parameter.MAWBPrefix))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", (string.Format("{0}", parameter.MAWBSerial))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", (string.Format("{0}", parameter.HAWB))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", (string.Format("{0}", parameter.Carrier))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", (string.Format("{0}", parameter.FlightNo))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", (string.Format("{0}", parameter.AgentCode))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", (string.Format("{0}", parameter.ScaleId))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", (string.Format("{0}", parameter.SlipNo))));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentype", (parameter.SipmentType)));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchLocation", (string.Format("{0}", parameter.Location))));
                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        /// <summary>
        /// search warehouse inventory
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchWhInventoryWithDateRange(SearchCriteria parameter, string fromDateTime, string toDateTime)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append("usp_SearchWHInventoryWithDate");

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                if (parameter.FlightDate == DateTime.MinValue)
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", null));
                }
                else
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", parameter.MAWB));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MAWBPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MAWBSerial));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.HAWB));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", parameter.Carrier));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", parameter.FlightNo));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", parameter.AgentCode));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", parameter.ScaleId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", parameter.SlipNo));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentype", (parameter.SipmentType)));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchLocation", (string.Format("{0}", parameter.Location))));
                cmd.Parameters.Add(entities.CreateParameter("@in_dtFromDateTime", fromDateTime));
                cmd.Parameters.Add(entities.CreateParameter("@in_dtToDateTime", toDateTime));

                //cmd.Parameters.AddWithValue("@in_vchApplicationID", appId);
                //cmd.Parameters.AddWithValue("@in_dtFlightDate", dpkFlightDate.Value);
                //cmd.Parameters.AddWithValue("@in_vchDevice", GlobalVariable.Device);
                //cmd.Parameters.AddWithValue("@in_vchUserID", GlobalVariable.userName);
                //cmd.Parameters.AddWithValue("@in_vchMAWB", txtPrefix.Text + "-" + txtSerial.Text);
                //cmd.Parameters.AddWithValue("@in_vchMAWBPrefix", "%" + txtPrefix.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchMAWBSerial", "%" + txtSerial.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchHAWB", "%" + txtHAWB.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchCarrier", "%" + txtCarrier.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchFlightNumber", "%" + txtFltNo.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchAgentCode", "");
                //cmd.Parameters.AddWithValue("@in_vchScaleID", "%" + txtScaleID.Text);
                //cmd.Parameters.AddWithValue("@in_vchSlipNumber", "%" + txtSlipNumber.Text + "%");
                //cmd.Parameters.AddWithValue("@in_vchShipmentype", "");

                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        /// <summary>
        /// search warehouse location
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchWhLocationDetail(SearchCriteria parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            DataSet ds = new DataSet();
            sqlString.Append("SELECT location,piece_count,inventory_id,location_id FROM v_hht_inventory WHERE slip_id=@slip_id AND mawb_id=@mawb_id");
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        /// <summary>
        /// count
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int CountLocationTotal(SearchCriteria parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            int count = 0;
            string temp = string.Empty;
            sqlString.Append("SELECT sum(piece_count) as total FROM v_hht_inventory WHERE slip_id=@slip_id AND mawb_id=@mawb_id");
            sqlString.Replace("@slip_id", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            sqlString.Replace("@mawb_id", BaseSQLModel.SqlWithQoute(parameter.MAWBId));
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                //cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                //cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    temp = reader["total"].ToString();
                    if (!string.IsNullOrEmpty(temp))
                    {
                        int.TryParse(temp, out count);
                    }
                }
            }

            return count;
        }
        /// <summary>
        /// update pickup item
        /// </summary>
        /// <param name="parameter"></param>
        public void UpdatePickItem(SearchCriteria parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            int affectedRecords = 0;
            sqlString.Append("UPDATE t_eics_mawb SET active_pick='YES' WHERE  mawb_id=@mawb_id");
            //sqlString.Replace("@slip_id", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@mawb_id", BaseSQLModel.SqlWithQoute(parameter.MAWBId));
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                affectedRecords = cmd.ExecuteNonQuery();
                if (affectedRecords < 0)
                {

                }
            }
        }
        /// <summary>
        /// update return item
        /// </summary>
        /// <param name="parameter"></param>
        public void UpdateReturnItem(SearchCriteria parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            int affectedRecords = 0;
            sqlString.Append("UPDATE t_eics_mawb SET active_pick='NO' WHERE  mawb_id=@mawb_id");
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                affectedRecords = cmd.ExecuteNonQuery();
                if (affectedRecords < 0)
                {

                }
            }
        }

        public DataSet SearchInventory(SearchCriteria parameter)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append("SELECT location,piece_count,inventory_id,location_id FROM v_hht_inventory WHERE slip_id=@slip_id AND mawb_id=@mawb_id");
                sqlString.Replace("@mawb_id", BaseSQLModel.SqlWithQoute(parameter.MAWBId));
                sqlString.Replace("@slip_id", BaseSQLModel.SqlWithQoute(parameter.SlipId));
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                //cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                //cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// count location
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int CountLocationTotalX(SearchCriteria parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            int count = 0;
            string temp = string.Empty;
            sqlString.Append("SELECT sum(piece_count) as total FROM v_hht_inventory WHERE slip_id=@slip_id AND mawb_id=@mawb_id");
            sqlString.Replace("@mawb_id", BaseSQLModel.SqlWithQoute(parameter.MAWBId));
            sqlString.Replace("@slip_id", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());

                //cmd.Parameters.Add(entities.CreateParameter("@slip_id", parameter.SlipId));
                //cmd.Parameters.Add(entities.CreateParameter("@mawb_id", parameter.MAWBId));
                DbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    temp = reader["total"].ToString();
                    if (!string.IsNullOrEmpty(temp))
                    {
                        int.TryParse(temp, out count);
                    }
                }
            }

            return count;
        }
        public DataSet ValidateMAWB(SearchCriteria parameter)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand("usp_ValidateMAWB");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MAWBPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MAWBSerial));
                if (!string.IsNullOrEmpty(parameter.HAWB))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.HAWB));
                }
                if (!string.IsNullOrEmpty(parameter.Carrier))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", parameter.Carrier));
                }
                if (!string.IsNullOrEmpty(parameter.FlightNo))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", parameter.FlightNo));
                }
                if (!string.IsNullOrEmpty(parameter.FlightDateString))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDateString));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        public DataSet GetMAWB(SearchCriteria parameter)
        {
            string hawb = string.Empty;
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand("usp_GetMAWB");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", parameter.MAWB));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MAWBPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MAWBSerial));
                if (!string.IsNullOrEmpty(parameter.HAWB))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.HAWB));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));

                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
                //if (ds != null && ds.Tables[0].Rows.Count > 0)
                //{
                //    if (ds.Tables[0].Rows[0]["status"].ToString() == "PASS")
                //    {
                //        hawb = ds.Tables[0].Rows[0]["hawb"].ToString();
                //    }
                //    else
                //    {
                //        CommonMessageBox.ShowMessageError(ds.Tables[0].Rows[0]["Message"].ToString());
                //    }
                //}
            }
            return ds;
        }
        /// <summary>
        /// Search Agent
        /// </summary>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public DataSet AgentSearch(SearchCriteria criteria)
        {
            DataSet ds = new DataSet();
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand("usp_GetAgentSearch");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", criteria.AppId));
                if (!string.IsNullOrEmpty(criteria.AgentCode))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", criteria.AgentCode));
                }
                if (!string.IsNullOrEmpty(criteria.AgentName))
                {
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentName", criteria.AgentName));
                }
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", criteria.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", criteria.UserId));

                DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                adapter.Fill(ds);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["status"].ToString() == "PASS")
                    {

                    }
                    else
                    {
                        CommonMessageBox.ShowMessageError(ds.Tables[0].Rows[0]["Message"].ToString());
                    }
                }
            }
            return ds;
        }
        #endregion
    }
}
